# -*- coding: utf-8 -*-

import uuid
import logging
from datetime import datetime

from sqlalchemy import MetaData, Table, Column, Integer, String, Float, DateTime, select, insert

from py_engine import engine

logger = logging.getLogger(__name__)

metadata = MetaData(bind=engine)


def test_metadata():
    table = Table(
        'table_name', metadata,
        # 主键，自增
        Column('primary_key', Integer(), primary_key=True, autoincrement=True, ),
        # 非空，唯一，索引
        Column('unique_index', String(length=32), nullable=False, unique=True, index=True, ),
        # 列名：decimal，
        Column('column_name', Float(precision=2, asdecimal=True, decimal_return_scale=4), ),
        # 默认值，更新触发赋值
        Column('update_datetime', DateTime(), default=datetime.now(), onupdate=datetime.now(), ),

    )
    # 创建表格
    metadata.create_all(bind=engine, tables=[table], checkfirst=True)

    # 插入数据
    insert_sql = table.insert().values(unique_index=uuid.uuid4().hex, column_name=108912.345678)
    logger.info(insert_sql)
    result = engine.execute(insert_sql)
    logger.info(f'insert rowcount:{result.rowcount}')
    if not result.rowcount:
        logger.error('insert failure')
        return
    # 插入成功获取插入主键
    primary_key = result.lastrowid
    logger.info(f'insert primary_key:{primary_key}')

    # 查询数据
    table.select(whereclause=table.columns.column_name <= 999999)
    data = engine.execute(select(columns=table.columns)).fetchall()
    logger.info(f'fetchall length:{len(data)}')
    for item in data:
        logger.info(f'column_name:{item["column_name"]}')
        pass

    # 更新数据
    update_sql = table.update().where(table.columns.primary_key == primary_key).values(column_name=9123456.789123)
    logger.debug(update_sql)
    result = engine.execute(update_sql)
    logger.info(f'update rowcount:{result.rowcount}')

    # 查询数据
    select_sql = select(columns=[table.columns.column_name]).where(table.columns.primary_key == primary_key)
    logger.debug(select_sql)
    item = engine.execute(select_sql).fetchone()
    logger.info(f'column_name:{item["column_name"]}')

    statement = insert(table).values(
        primary_key=primary_key,
        column_name=123445346.1235234756,
    ).on_duplicate_key_update(column_name=4587987.2380235)

    # 删除数据
    delete_sql = table.delete().where(table.columns.primary_key == primary_key)
    logger.debug(delete_sql)
    result = engine.execute(update_sql)
    logger.info(f'delete rowcount:{result.rowcount}')

    pass


def test_autoload():
    user_table = Table('user', metadata, autoload=True, autoload_with=engine)
    logger.info([column.name for column in user_table.c])


if __name__ == '__main__':
    test_autoload()
    pass
